import hashlib
import sqlite3
import time
import MySQLdb
from scrapyProject.spiders.DataBase import  databaseCore

class proxyManageForSQLite(databaseCore):
    def __init__(self):
        self.m_conn = sqlite3.connect('.\\DataBase\\tinaya.db')

    _instance = None

    def __new__(cls, *args, **kw):
        if not cls._instance:
            cls._instance = super(proxyManageForSQLite, cls).__new__(cls, *args, **kw)
        return cls._instance

    def __del__(self):
        self.m_conn.close()

    def findData(self, idUrl):
        c = self.m_conn.cursor()
        cursor = c.execute("select urlProxy, scheme,lastCheckTime,DelayedTime, TotalDelayedTime/checkNum  from proxy where id = ? order by  TotalDelayedTime/checkNum ", idUrl)

        ret={}
        if cursor.rowcount > 0:
            ret["hasData"] = True
            for row in cursor.fetchall():
                ret["proxy"] = row[0]
                ret["scheme"] = row[1]
                ret["lastCheckTime"] = row[2]
                ret["DelayedTime"] = row[3]
                ret["aveDelayedTime"] = row[4]
            return [True, ret]
        return [False, ret]

    def proxyInsert(self, proxy, proxy_scheme, lastCheckTime, location, delayedTime):
        sql=""
        if not self.findID(proxy=proxy) :
            sql = 'insert into proxy(id, urlProxy, scheme, lastCheckTime, location, DelayedTime, TotalDelayedTime, checkNum) VALUES (?, ?, ?, ?, ?,?,? ,?)'
            data = (self.getID(proxy), proxy,proxy_scheme, lastCheckTime, location, delayedTime, delayedTime, 1)
        else:
            sql = 'update proxy set lastCheckTime=?, location =?, DelayedTime=?, TotalDelayedTime=TotalDelayedTime+%s, checkNum=checkNum+1 where id = ?'%str(delayedTime)
            data = (lastCheckTime, location, delayedTime, self.getID(proxy) )
        self.m_conn.execute(sql, data)
        self.m_conn.commit()

    def proxyDelete(self, proxy):
        c = self.m_conn.cursor()
        idUrl = self.getID(url = proxy)
        c.execute("delete from proxy where id = ?", idUrl)

    def proxyDeleteExpired(self, aveMaxExceedSpeed):
        sql = "delete from proxy where  TotalDelayedTime/checkNum > %s"%aveMaxExceedSpeed
        self.m_conn.execute(sql)
        self.m_conn.commit()

    def GetAllData(self):
        c = self.m_conn.cursor()
        cursor = c.execute("select urlProxy, scheme,lastCheckTime,DelayedTime, TotalDelayedTime/checkNum from proxy order by  TotalDelayedTime/checkNum")

        for row in cursor:
             retLine = {}
             retLine["proxy"] = row[0]
             retLine["scheme"] = row[1]
             retLine["lastCheckTime"] = row[2]
             retLine["DelayedTime"] = row[3]
             retLine["aveDelayedTime"] = row[4]
             yield retLine


    def proxyUpdateTimeOut(self, proxy, lastCheckTime, delayedTime):
        if self.findID(proxy=proxy):
            sql = 'update proxy set lastCheckTime=?,DelayedTime=?, TotalDelayedTime=TotalDelayedTime+%s, checkNum=checkNum+1 where id = ?'%str(delayedTime)
            data = (lastCheckTime, delayedTime, self.getID(proxy))
            self.m_conn.execute(sql, data)
            self.m_conn.commit()

    def proxyUpdateInfo(self):
        sql = 'update proxyInfo set lastUpdateTime=%s where id = 0'%str(time.time())
        self.m_conn.execute(sql)
        self.m_conn.commit()

    def getValidData(self, aveSpeed=100, lastSpeed=100,maxCount=100):
        c = self.m_conn.cursor()
        sql = "select urlProxy, scheme,lastCheckTime,DelayedTime, TotalDelayedTime/checkNum from proxy where  TotalDelayedTime/checkNum <? and DelayedTime < ? order by id limit 0,? "
        data=(aveSpeed, lastSpeed, maxCount)
        cursor = c.execute(sql, data)
        ret = []
        for row in cursor:
            retLine = {}
            retLine["proxy"] = row[0]
            retLine["scheme"] = row[1]
            retLine["lastCheckTime"] = row[2]
            retLine["DelayedTime"] = row[3]
            retLine["aveDelayedTime"] = row[4]
            yield retLine

    def isNeedUpdate(self, timeNow = time.time()):
        sql = 'select * from proxyInfo where id = 0'
        cursor =    self.m_conn.execute(sql)
        isNeedUpdate = False
        for row in cursor:
            if timeNow <row[1]:
                isNeedUpdate = True
            break
        return  isNeedUpdate

class proxyManageForMySQL(databaseCore):


    def findData(self, idUrl):
        cursor = self.m_conn.cursor()
        cursor.execute(
            "select urlProxy, scheme,lastCheckTime,DelayedTime, TotalDelayedTime/checkNum  from proxy where id = %s order by  TotalDelayedTime/checkNum ",
            idUrl)

        ret = {}
        if cursor.rowcount > 0:
            ret["hasData"] = True
            for i in range(cursor.rowcount):
                row = cursor.fetchone()
                ret["proxy"] = row[0]
                ret["scheme"] = row[1]
                ret["lastCheckTime"] = row[2]
                ret["DelayedTime"] = row[3]
                ret["aveDelayedTime"] = row[4]
            return [True, ret]
        return [False, ret]

    def proxyInsert(self, proxy, proxy_scheme, lastCheckTime, location, delayedTime):
        sql = ""
        if not self.findID(proxy=proxy):
            sql = 'insert into proxy(id, urlProxy, scheme, lastCheckTime, location, DelayedTime, TotalDelayedTime, checkNum) VALUES (%s, %s, %s, %s, %s,%s,%s ,%s)'
            data = (self.getID(proxy), proxy, proxy_scheme, lastCheckTime, location, delayedTime, delayedTime, 1)
        else:
            sql = 'update proxy set lastCheckTime=%s, location =%s, DelayedTime=%s, TotalDelayedTime=TotalDelayedTime+%s, checkNum=checkNum+1 where id = %s'
            data = (lastCheckTime, location, delayedTime, str(delayedTime), self.getID(proxy))
        c = self.m_conn.cursor()
        c.execute(sql, data)
        self.m_conn.commit()

    def proxyDelete(self, proxy):
        c = self.m_conn.cursor()
        idUrl = self.getID(url=proxy)
        c.execute("delete from proxy where id = %s", idUrl)
        self.m_conn.commit()

    def proxyDeleteExpired(self, aveMaxExceedSpeed):
        c = self.m_conn.cursor()
        sql = "delete from proxy where  TotalDelayedTime/checkNum > %s" % aveMaxExceedSpeed
        c.execute(sql)
        self.m_conn.commit()

    def GetAllData(self):
        cursor = self.m_conn.cursor()
        cursor.execute(
            "select urlProxy, scheme,lastCheckTime,DelayedTime, TotalDelayedTime/checkNum from proxy order by  TotalDelayedTime/checkNum")
        for i in range(cursor.rowcount):
            row = cursor.fetchone()
            retLine = {}
            retLine["proxy"] = row[0]
            retLine["scheme"] = row[1]
            retLine["lastCheckTime"] = row[2]
            retLine["DelayedTime"] = row[3]
            retLine["aveDelayedTime"] = row[4]
            yield retLine

    def proxyUpdateTimeOut(self, proxy, lastCheckTime, delayedTime, IsUsePenalty = True):
        if self.findID(proxy=proxy):
            if IsUsePenalty:
                sql = 'update proxy set lastCheckTime=%s,DelayedTime=%s, TotalDelayedTime=DelayedTime+TotalDelayedTime+%s, checkNum=checkNum+1 where id = %s'
            else:
                sql = 'update proxy set lastCheckTime=%s,DelayedTime=%s, TotalDelayedTime=TotalDelayedTime+%s, checkNum=checkNum+1 where id = %s'
            data = (lastCheckTime, delayedTime, str(delayedTime), self.getID(proxy))
            cursor = self.m_conn.cursor()
            cursor.execute(sql, data)
            self.m_conn.commit()

    def proxyUpdateInfo(self):
        sql = 'update proxyInfo set lastUpdateTime=%s where id = 0' % str(time.time())
        self.m_conn.cursor().execute(sql)
        self.m_conn.commit()

    def getValidData(self, aveSpeed=200, lastSpeed=80, maxCount=100):
        cursor = self.m_conn.cursor()
        sql = "select urlProxy, scheme,lastCheckTime,DelayedTime, TotalDelayedTime/checkNum from proxy where (TotalDelayedTime/checkNum <%s and DelayedTime < %s) or DelayedTime<%s order by id limit 0,%s "
        data = (aveSpeed, lastSpeed,lastSpeed*0.5, maxCount)
        cursor.execute(sql, data)
        ret = []

        for i in range(cursor.rowcount):
            row = cursor.fetchone()
            retLine = {}
            retLine["proxy"] = row[0]
            retLine["scheme"] = row[1]
            retLine["lastCheckTime"] = row[2]
            retLine["DelayedTime"] = row[3]
            retLine["aveDelayedTime"] = row[4]
            yield retLine

    def isNeedUpdate(self, timeNow=time.time()):
        cursor = self.m_conn.cursor()
        sql = 'select * from proxyInfo where id = 0'
        cursor.execute(sql)
        isNeedUpdate = False
        for row in cursor.fetchall():
            if timeNow < row[1]:
                isNeedUpdate = True
            break
        return isNeedUpdate